package com.fau.wm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.wm.entity.MedicalAdmin;
import com.fau.wm.entity.MedicalKeyValue;
import com.fau.wm.entity.MedicalMedicine;
import com.fau.wm.entity.MedicalMedicineAppend;
import com.fau.wm.entity.MedicalStorage;
import com.fau.wm.entity.MedicalStorageDetail;
import com.fau.wm.entity.MedicalWarehouse;
import com.fau.wm.service.IStorageManagerService;
import com.fau.wm.util.Page;
import com.fau.wm.util.ParamUtil;
import com.fau.wm.util.SystemConfig;
import com.fau.wm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class StorageManagerService extends BaseService implements
		IStorageManagerService {

	@Override
	public Page findStorageByPage(MedicalStorage storage, Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalStorage a where a.hospitalId = ? and a.status in(?,?,?) ");
		StringBuilder statusSql = new StringBuilder("from MedicalStorage a where a.hospitalId = ? and a.status in(?,?,?) ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(storage.getHospitalId());
		objectList.add(SystemConfig.STORAGE_STATUS_APPROVAL);
		objectList.add(SystemConfig.STORAGE_STATUS_REJECT);
		objectList.add(SystemConfig.STORAGE_STATUS_NEW);
		
		if(StringUtils.isNotBlank(storage.getTitle())){
			countSql.append("and a.title like ? ");
			statusSql.append("and a.title like ? ");
			objectList.add("%"+storage.getTitle()+"%");
		}
		
		if(StringUtils.isNotBlank(storage.getSerialNum())){
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(storage.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(storage.getWarehouseName())){
			countSql.append("and a.warehouse.name like ? ");
			statusSql.append("and a.warehouse.name  like ? ");
			objectList.add("%"+storage.getWarehouseName()+"%");
		}
		
		//排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils .isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])){
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]); 
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalStorage> storageList = new ArrayList<MedicalStorage>();
		for(Object obj : page.getResult()){
			storage = (MedicalStorage)obj;
			MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(storage.getHospitalId(), storage.getType());
			storage.setTypeVal(keyVal.getKeyValue());
			storageList.add(storage);
		}
		page.setResult(storageList);
		return page;
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveStorage(MedicalStorage storage, MedicalAdmin writeUser) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(storage);
		if(errorInfo.size()>0){
			return false;
		}else{
			storage.setHospitalId(writeUser.getEnterpriseId());
			MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, storage.getWarehouseId());
			storage.setWarehouse(warehouse);
			storage.setWriteUser(writeUser);
			return this.iBaseDao.saveObject(storage);
		}
	}
	
	@Override
	public Boolean verSerialNum(String serialNum, String hospitalId){
		MedicalStorage storage = (MedicalStorage)this.iBaseDao.getObjectByHql("from MedicalStorage a where a.serialNum=? and a.hospitalId=? ", serialNum, hospitalId);
		if(null == storage){
			return true;
		}else{
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveEditStorage(MedicalStorage storage) {
		MedicalStorage bean = (MedicalStorage) this.iBaseDao.getObject(MedicalStorage.class, storage.getId());
		MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, storage.getWarehouseId());
		bean.setWarehouse(warehouse);
		bean.setSerialNum(storage.getSerialNum());
		bean.setTitle(storage.getTitle());
		bean.setStorageTime(storage.getStorageTime());
		bean.setUpdateTime(new Date());
		this.iBaseDao.saveObject(bean);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delStorage(MedicalStorage storage) {
		String[] ids = storage.getId().split("-");
		for (String id : ids) {
			this.iBaseDao.updateHql("delete from MedicalStorageDetail a where a.storage.id=?", id);
			this.iBaseDao.updateHql("delete from MedicalMedicineAppend a where a.storageId=?", id);
			this.iBaseDao.updateHql("delete from MedicalStorage a where a.id=?", id);
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveStorageDetail(MedicalStorageDetail storageDetail) {
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil.validateObj(storageDetail);
		if(errorInfo.size()>0){
			return false;
		}else{
			MedicalMedicineAppend medicineAppend = new MedicalMedicineAppend();
			medicineAppend.setProductionDate(storageDetail.getProductionDate());
			medicineAppend.setValidTo(storageDetail.getValidTo());
			medicineAppend.setMedicineId(storageDetail.getMedicineId());
			medicineAppend.setStorageId(storageDetail.getStorageId());
			this.iBaseDao.saveObject(medicineAppend);
			storageDetail.setMedicineAppendId(medicineAppend.getId());
			MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, storageDetail.getMedicineId());
			storageDetail.setMedicine(medicine);
			MedicalStorage storage = (MedicalStorage) this.iBaseDao.getObject(MedicalStorage.class, storageDetail.getStorageId());
			storageDetail.setStorage(storage);
			return this.iBaseDao.saveObject(storageDetail);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalStorageDetail> findStorageDetailList(String storageId) {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		List<MedicalStorageDetail> list = this.iBaseDao.getBySql("from MedicalStorageDetail a where a.storage.id=? ", null, storageId);
		for (MedicalStorageDetail medicalStorageDetail : list) {
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), medicalStorageDetail.getMedicine().getUnit());
			medicalStorageDetail.getMedicine().setUnitValue(kv.getKeyValue());
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Page findMedicineStorageDetailList(String medicineId,String warehouseId,Map<String, Object> map) {
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalStorageDetail a where a.medicine.id=? and a.storage.warehouse.id=? and a.storage.status=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalStorageDetail a where a.medicine.id=? and a.storage.warehouse.id=? and a.storage.status=? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(medicineId);
		objectList.add(warehouseId);
		objectList.add(SystemConfig.STORAGE_STATUS_APPROVAL);
		
		statusSql.append("order by a.createTime desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		
		List<MedicalStorageDetail> list = (List<MedicalStorageDetail>) page.getResult();
		for (MedicalStorageDetail medicalStorageDetail : list) {
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), medicalStorageDetail.getMedicine().getUnit());
			medicalStorageDetail.getMedicine().setUnitValue(kv.getKeyValue());
		}
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delStorageDetail(MedicalStorageDetail storageDetail) {
		MedicalStorageDetail bean = (MedicalStorageDetail) this.iBaseDao.getObject(MedicalStorageDetail.class, storageDetail.getId());
		MedicalMedicineAppend medicineAppend = (MedicalMedicineAppend) this.iBaseDao.getObject(MedicalMedicineAppend.class, bean.getMedicineAppendId());
		this.iBaseDao.deleteObject(medicineAppend);
		this.iBaseDao.deleteObject(bean);
	}

	@Override
	public MedicalStorageDetail findStorageDetailById(String id) {
		 MedicalStorageDetail storageDetail = (MedicalStorageDetail) this.iBaseDao.getObject(MedicalStorageDetail.class, id);
		 MedicalMedicineAppend medicineAppend = (MedicalMedicineAppend) this.iBaseDao.getObject(MedicalMedicineAppend.class, storageDetail.getMedicineAppendId());
		 storageDetail.setProductionDate(medicineAppend.getProductionDate());
		 storageDetail.setValidTo(medicineAppend.getValidTo());
		return storageDetail;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void editSaveStorageDetail(MedicalStorageDetail storageDetail) {
		MedicalStorageDetail bean = (MedicalStorageDetail) this.iBaseDao.getObject(MedicalStorageDetail.class, storageDetail.getId());
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, storageDetail.getMedicineId());
		bean.setMedicine(medicine);
		bean.setQuantity(storageDetail.getQuantity());
		MedicalMedicineAppend medicineAppend = (MedicalMedicineAppend) this.iBaseDao.getObject(MedicalMedicineAppend.class, bean.getMedicineAppendId());
		medicineAppend.setProductionDate(storageDetail.getProductionDate());
		medicineAppend.setValidTo(storageDetail.getValidTo());
		medicineAppend.setMedicineId(medicine.getId());
		
		bean.setUpdateTime(new Date());
		this.iBaseDao.saveObject(bean);
	}

	@Override
	public Boolean checkMedicineExist(String storageId, String medicineId) {
		MedicalStorageDetail storageDetail = (MedicalStorageDetail) this.iBaseDao.getObjectByHql("from MedicalStorageDetail a where 1=1 and a.storage.id=? and a.medicine.id=? ", storageId, medicineId);
		if(null == storageDetail){
			return true;
		}else{
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateStorageStatus(String status, String id) {
		MedicalStorage storage = (MedicalStorage) this.iBaseDao.getObject(MedicalStorage.class, id);
		storage.setStatus(status);
	}

	@Override
	public Page findStorageAuditByPage(Map<String, Object> map,
			MedicalStorage storage) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalStorage a where 1=1 and a.hospitalId = ? ");
		StringBuilder statusSql = new StringBuilder("from MedicalStorage a where 1=1 and a.hospitalId = ? ");
		
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(storage.getHospitalId());
		
		if(StringUtils.isNotBlank(storage.getTitle())){
			countSql.append("and a.title like ? ");
			statusSql.append("and a.title like ? ");
			objectList.add("%"+storage.getTitle()+"%");
		}
		
		if(StringUtils.isNotBlank(storage.getSerialNum())){
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(storage.getSerialNum());
		}
		
		if(StringUtils.isNotBlank(storage.getWarehouseName())){
			
			countSql.append("and a.warehouse.name like ? ");
			statusSql.append("and a.warehouse.name like ? ");
			objectList.add("%"+storage.getWarehouseName()+"%");
		}
		
		countSql.append("and a.status=? ");
		statusSql.append("and a.status=? ");
		objectList.add("0");
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		List<MedicalStorage> storageList = new ArrayList<MedicalStorage>();
		for(Object obj : page.getResult()){
			storage = (MedicalStorage)obj;
			MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(storage.getHospitalId(), storage.getType());
			storage.setTypeVal(keyVal.getKeyValue());
			storageList.add(storage);
		}
		page.setResult(storageList);
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateAuditUser(MedicalAdmin auditUser, String storageId) {
		MedicalStorage storage = (MedicalStorage) this.iBaseDao.getObject(MedicalStorage.class, storageId);
		storage.setAuditUser(auditUser);
	}
	
	@SuppressWarnings({ "unchecked" })
	@Override
	public Workbook exportStorageData(String id) throws Exception{
		MedicalStorage bean = (MedicalStorage) this.iBaseDao.getObject(MedicalStorage.class, id);
		List<MedicalStorageDetail> medicineDetailList=this.iBaseDao.getBySql("from MedicalStorageDetail where storage.id=?" , null, bean.getId());
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		String realPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.STORAGE_RECALL);
		File file = new File(realPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		CellStyle style = workbook.createCellStyle();
		style.setWrapText(true);
		
		Cell cell=null;
		Sheet sheet = workbook.getSheetAt(0);
		cell = sheet.getRow(1).getCell(1);
		cell.setCellValue(bean.getSerialNum());
		cell = sheet.getRow(1).getCell(3);
		cell.setCellValue(bean.getTitle());
		cell = sheet.getRow(1).getCell(6);
		cell.setCellValue(bean.getWarehouse().getName());
		cell = sheet.getRow(1).getCell(8);
		MedicalKeyValue keyVal = this.iBaseDao.getValueByKey(user.getEnterpriseId(), bean.getType());
		cell.setCellValue(keyVal.getKeyValue());
		cell = sheet.getRow(2).getCell(1);
		cell.setCellValue(DateFormatUtils.format(bean.getCreateTime(),"yyyy-MM-dd"));
		MedicalAdmin write=(MedicalAdmin) this.iBaseDao.getObjectByHql("from MedicalAdmin where id=? " , bean.getWriteUser().getId());
		cell= sheet.getRow(2).getCell(5);
		cell.setCellValue(write.getName());
		
		for(int i=0;i<medicineDetailList.size();i++){
			String name=medicineDetailList.get(i).getMedicine().getName();
			cell=sheet.getRow(4+i).getCell(0);
			cell.setCellValue(name);
			Integer quantity=medicineDetailList.get(i).getQuantity();
			cell=sheet.getRow(4+i).getCell(3);
			cell.setCellValue(quantity);
			MedicalMedicineAppend mdicineAppend=(MedicalMedicineAppend)this.iBaseDao.getObjectByHql("from MedicalMedicineAppend a where a.medicineId=? ",medicineDetailList.get(i).getMedicine().getId());
			cell=sheet.getRow(4+i).getCell(4);
			cell.setCellValue(DateFormatUtils.format(mdicineAppend.getProductionDate(),"yyyy-MM-dd"));
			cell=sheet.getRow(4+i).getCell(6);
			cell.setCellValue(DateFormatUtils.format(mdicineAppend.getValidTo(),"yyyy-MM-dd"));
		}
		
		return workbook;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String improtStorageMedicine(MedicalAdmin admin,String sid, File file) throws Exception {
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		for (Row row : sheet) {
			// 跳过表头
			if(row.getRowNum() < 1){
				continue;
			}
			if(null == row.getCell(0)){
				break;
			}
			
			MedicalStorageDetail storageDetail = new MedicalStorageDetail();
			MedicalStorage storage = (MedicalStorage) this.iBaseDao.getObject(MedicalStorage.class, sid);
			storageDetail.setStorage(storage);
			
			String serialNum = null;
			if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()){
				serialNum = row.getCell(0).getStringCellValue().trim(); //药品编号     
			}else{
				serialNum = String.format("%.0f",row.getCell(0).getNumericCellValue());
			}
			
			MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObjectByHql("from MedicalMedicine a where a.hospitalId=? and a.serialNum=? ", admin.getEnterpriseId(),serialNum);
			if(null == medicine){
				return "药品不存在";
			}else{
				storageDetail.setMedicine(medicine);
			}
			
			String num = null;
			if(null == row.getCell(3)){
				return "入库量不能为空";
			}else{
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(3).getCellType()){
					num = row.getCell(3).getStringCellValue().trim(); //入库量
				}else{
					num = String.format("%.0f",row.getCell(3).getNumericCellValue());
				}
				storageDetail.setQuantity(Integer.valueOf(num));
			}
			
			this.iBaseDao.saveObject(storageDetail);
			
			MedicalMedicineAppend medicineAppend = new MedicalMedicineAppend();
			
			if(null != row.getCell(1)){
				if(HSSFCell.CELL_TYPE_NUMERIC == row.getCell(1).getCellType()){
					Date d1 = row.getCell(1).getDateCellValue();
					medicineAppend.setProductionDate(d1);
				}else{
					return "生产日期格式不正确";
				}
			}
			
			if(null != row.getCell(2)){
				if(HSSFCell.CELL_TYPE_NUMERIC == row.getCell(2).getCellType()){
					Date d2 = row.getCell(2).getDateCellValue();
					medicineAppend.setValidTo(d2);
				}else{
					return "有效期至格式不正确";
				}
			}
			medicineAppend.setMedicineId(medicine.getId());
			medicineAppend.setStorageId(sid);
			this.iBaseDao.saveObject(medicineAppend);
			storageDetail.setMedicineAppendId(medicineAppend.getId());
		}
		
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean toAudit(String sid) {
		List<MedicalStorageDetail> list = this.iBaseDao.getBySql("from MedicalStorageDetail a where a.storage.id=? ", null, sid);
		if(list.size() < 1){
			return false;
		}else{
			this.iBaseDao.updateHql("update MedicalStorage a set a.status=? where a.id=? ", SystemConfig.STORAGE_STATUS_PENDING,sid);
			return true;
		}
	}
	
}
